﻿using DevelopAssistant.Service;
using ICSharpCode.WinFormsUI.Docking;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;

namespace DevelopAssistant.Core.DBMS
{
    public partial class TableDataEditorDocument : DockContent, IDocumentContent
    {
        delegate int Delegate_Page_BindData();

        private bool showRowId = false;
        private string _table;
        private string _orderByColumn;
        private DataBaseServer _server;        
        private List<string> _columns = new List<string>();       

        public TableDataEditorDocument()
        {
            InitializeComponent();
            InitializeControls();
        }

        public TableDataEditorDocument(string TableName,DataBaseServer DatabaseServer)
            : this()
        {
            this.Text = TableName;
            _table = TableName;
            _server = DatabaseServer;

            using (var db = Utility.GetAdohelper(_server))
            {
                DataTable dsk = db.GetTableObject(_table);

                _orderByColumn = string.Empty;
                _columns = new List<string>();

                foreach (DataRow dr in dsk.Rows)
                {
                    string columnName = dr["ColumnName"] + "";
                    string columnType = dr["TypeName"] + "";
                    string columnIsNull = dr["CisNull"] + "";

                    if (columnIsNull.Contains("pk"))
                    {
                        _orderByColumn = columnName;
                    }
                    else if (columnIsNull.Contains("indefity"))
                    {
                        _orderByColumn = columnName;
                    }

                    if (!columnName.StartsWith("as", StringComparison.OrdinalIgnoreCase))
                    {
                        _columns.Add(columnName);
                    }
                    
                }
            }

            this.toolStripTextBox1.Focus();
 
        }

        private void InitializeControls()
        {
            this.dgv_list.MouseClick += dgv_list_MouseClick;
            this.dgv_list.CellMouseClick += dgv_list_CellMouseClick;
        }

        public void FindAndReplace(FindAndReplaceRequest request)
        {

        }

        private void TableDataEditorDocument_Load(object sender, EventArgs e)
        {
            OnThemeChanged(new EventArgs());
        }

        public void InitializeData()
        {
            pager1.PageCurrent = 1;
            pager1.Bind();
        }

        private int BindPagerData()
        {
            int recordCount = 0;
            using (var db = Utility.GetAdohelper(_server))
            {
                try
                {
                    if (this.InvokeRequired)
                    {
                        this.Invoke(new MethodInvoker(() =>
                        {
                            pager1.Enabled = false;
                            toolStrip1.Enabled = false;
                        }));
                    }
                    else
                    {
                        pager1.Enabled = false;
                        toolStrip1.Enabled = false;
                    }
                    NORM.Entity.PageLimit pageLimit = new NORM.Entity.PageLimit();
                    pageLimit.PageIndex = pager1.PageCurrent;
                    pageLimit.PageSize = pager1.PageSize;

                    if (_columns == null && _columns.Count < 1)
                        return 0;

                    if (string.IsNullOrEmpty(_orderByColumn))
                    {
                        _orderByColumn = _columns.First();
                    }

                    DataTable dt = new DataTable();
                    GetPagerData(pageLimit, db, out dt);
                    recordCount = pageLimit.RecordCount;

                    if (this.InvokeRequired)                       
                          this.Invoke(new MethodInvoker(delegate() { dgv_list.DataSource = dt.DefaultView; }));
                    else
                        dgv_list.DataSource = dt.DefaultView;


                    if (this.InvokeRequired)
                    {
                        this.Invoke(new MethodInvoker(delegate() {
                            pager1.Enabled = true;
                            toolStrip1.Enabled = true;
                        }));
                    }
                    else
                    {
                        pager1.Enabled = true;
                        toolStrip1.Enabled = true;
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            return recordCount;
        }

        private int pager1_EventPaging(ICSharpCode.WinFormsUI.Controls.EventPagingArgs e)
        {
            Delegate_Page_BindData d = new Delegate_Page_BindData(BindPagerData);
            return d.Invoke();
        }

        private int GetPagerData(NORM.Entity.PageLimit pager, NORM.DataBase.DataBase db,out DataTable dt)
        {
            int recordCount = 0;

            int pageSize=pager.PageSize ;
            int pageIndex = pager.PageIndex;
            int startIndex = (pager.PageIndex - 1) * pager.PageSize + 1;
            int endIndex = startIndex + pager.PageSize - 1;

            if (db.DataBaseType != NORM.DataBase.DataBaseTypes.SqlDataBase)
            {
                startIndex = (pager.PageIndex - 1) * pager.PageSize;
                endIndex = startIndex + pager.PageSize;
            }

            string sqlPageText = "SELECT * FROM ";
            string sqlTotalText = "SELECT COUNT(1) FROM ";

            switch (db.DataBaseType)
            {               
                case  NORM.DataBase.DataBaseTypes.SqlDataBase:
                    sqlPageText += "[" + _table + "]";
                    sqlTotalText += "[" + _table + "]";
                    if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                    {
                        string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                        string whereValue = toolStripTextBox1.Text.Split('=')[1];
                        sqlPageText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + "";
                        sqlTotalText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + "";
                    }
                    break;
                case NORM.DataBase.DataBaseTypes.SqliteDataBase:
                    sqlPageText += "[" + _table + "]";
                    sqlTotalText += "[" + _table + "]";
                    if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                    {
                        string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                        string whereValue = toolStripTextBox1.Text.Split('=')[1];
                        sqlPageText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + "";
                        sqlTotalText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + "";
                    }
                    break;
                case NORM.DataBase.DataBaseTypes.PostgreSqlDataBase:
                    sqlPageText += "\"" + _table + "\"";
                    sqlTotalText += "\"" + _table + "\"";
                    if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                    {
                        string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                        string whereValue = toolStripTextBox1.Text.Split('=')[1];
                        sqlPageText += "WHERE " + "\"" + whereFeild + "\"=" + "" + whereValue + "";
                        sqlTotalText += "WHERE " + "\"" + whereFeild + "\"=" + "" + whereValue + "";
                    }
                    break;
                case NORM.DataBase.DataBaseTypes.MySqlDataBase:
                    sqlPageText += "" + _table + "";
                    sqlTotalText += "" + _table + "";
                    if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                    {
                        string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                        string whereValue = toolStripTextBox1.Text.Split('=')[1];
                        sqlPageText += "WHERE " + whereFeild + "=" + "" + whereValue + "";
                        sqlTotalText += "WHERE " + whereFeild + "=" + "" + whereValue + "";
                    }
                    break;
            }            

            DataTable dt_recordCount = db.QueryDataSet(CommandType.Text, sqlTotalText, null).Tables[0];
            recordCount = Int32.Parse(dt_recordCount.Rows[0][0] + "");

            int pageCount = recordCount % pageSize > 0 ? recordCount / pageSize + 1 : recordCount / pageSize;
            if (pageIndex <= 1) pageIndex = 1;
            if (pageIndex >= pageCount) pageIndex = pageCount;

            string _sql = sqlPageText;
            switch (db.DataBaseType)
            {
                case NORM.DataBase.DataBaseTypes.SqlDataBase:

                    #region Sql 数据库

                    sqlPageText = string.Empty;
                    sqlPageText += "SELECT ";
                    if (showRowId)
                    {
                        sqlPageText += " * ";
                    }
                    else
                    {
                        int inum = 0;
                        foreach (string name in _columns)
                        {
                            if (inum > 0)
                                sqlPageText += ",";
                            sqlPageText += "[" + name + "] ";
                            inum++;
                        }                       
                    }
                    sqlPageText += "FROM (";
                    sqlPageText += "SELECT row_number() over (order by [" + _orderByColumn + "] ) as rowId";
                    sqlPageText += ",* FROM [" + _table + "] ";

                    //LoginLogID='811240'
                    if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                    {
                        string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                        string whereValue = toolStripTextBox1.Text.Split('=')[1];
                        sqlPageText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + "";
                        sqlTotalText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + "";
                    }

                    sqlPageText += ") t1 ";
                    sqlPageText += "WHERE rowId>=" + startIndex + " ";
                    sqlPageText += "AND rowId<=" + endIndex + "";

                    #endregion

                    break;

                case NORM.DataBase.DataBaseTypes.PostgreSqlDataBase:

                    #region PostgreSql 数据库

                    sqlPageText = string.Empty;
                   

                    if (showRowId)
                    {
                        sqlPageText += "create temp sequence rownum; ";
                        sqlPageText += "SELECT ";
                        sqlPageText += "nextval('rownum') as \"rowId\" , t1.* ";
                        sqlPageText += "FROM (  ";
                        sqlPageText += " SELECT * ";
                        sqlPageText += " FROM \""+_table+"\"";                       

                        //LoginLogID='811240'
                        if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                        {
                            string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                            string whereValue = toolStripTextBox1.Text.Split('=')[1];
                            sqlPageText += "WHERE " + "\"" + whereFeild + "\"=" + "" + whereValue + "";
                            sqlTotalText += "WHERE " + "\"" + whereFeild + "\"=" + "" + whereValue + "";
                        }

                        sqlPageText += " ORDER BY \"" + _orderByColumn + "\" ";

                        sqlPageText += ") t1 ";

                        sqlPageText += "LIMIT " + pageSize + " OFFSET " + startIndex + "; ";

                        sqlPageText += "drop sequence rownum; ";
                    }
                    else
                    {
                        sqlPageText += "SELECT ";
                        int nnum = 0;
                        foreach (string name in _columns)
                        {
                            if (nnum > 0)
                                sqlPageText += ",";
                            sqlPageText += "\"" + name + "\" ";
                            nnum++;
                        }
                        sqlPageText += "FROM \"" + _table + "\"";

                        //LoginLogID='811240'
                        if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                        {
                            string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                            string whereValue = toolStripTextBox1.Text.Split('=')[1];
                            sqlPageText += "WHERE " + "\"" + whereFeild + "\"=" + "" + whereValue + "";
                            sqlTotalText += "WHERE " + "\"" + whereFeild + "\"=" + "" + whereValue + "";
                        }

                        sqlPageText += " ORDER BY \"" + _orderByColumn + "\"";

                        sqlPageText += " LIMIT " + pageSize + " OFFSET " + startIndex + "; ";
                    }                    

                    #endregion

                    break;

                case NORM.DataBase.DataBaseTypes.SqliteDataBase:

                    #region Sqlite 数据库

                    sqlPageText = string.Empty;
                    sqlPageText += "SELECT ";

                    if (showRowId)
                    {
                        sqlPageText += " rowid as [rowId] , t1.* ";
                    }
                    else
                    {
                        int nnum = 0;
                        foreach (string name in _columns)
                        {
                            if (nnum > 0)
                                sqlPageText += ",";
                            sqlPageText += "[" + name + "] ";
                            nnum++;
                        }
                    }

                    sqlPageText += "FROM ( ";
                    sqlPageText += " SELECT * ";
                    sqlPageText += " FROM [" + _table + "] ";

                    if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                    {
                        string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                        string whereValue = toolStripTextBox1.Text.Split('=')[1];
                        sqlPageText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + " ";
                        sqlTotalText += "WHERE " + "[" + whereFeild + "]=" + "" + whereValue + " ";
                    }

                    sqlPageText += " ORDER BY [" + _orderByColumn + "] ";
                    sqlPageText += ") t1 ";
                    sqlPageText += "LIMIT " + pageSize + " OFFSET " + startIndex + ";";

                    #endregion

                    break;

                case NORM.DataBase.DataBaseTypes.MySqlDataBase:

                    #region MySql 数据库

                    sqlPageText = string.Empty;
                    sqlPageText += "SELECT ";

                    if (showRowId)
                    {
                        sqlPageText += " rowid as [rowId] , t1.* ";
                    }
                    else
                    {
                        int nnum = 0;
                        foreach (string name in _columns)
                        {
                            if (nnum > 0)
                                sqlPageText += ",";
                            sqlPageText += "" + name + " ";
                            nnum++;
                        }
                    }

                    sqlPageText += "FROM ( ";
                    sqlPageText += " SELECT * ";
                    sqlPageText += " FROM " + _table + " ";

                    if (!string.IsNullOrEmpty(toolStripTextBox1.Text) && toolStripTextBox1.Text.Contains("="))
                    {
                        string whereFeild = toolStripTextBox1.Text.Split('=')[0];
                        string whereValue = toolStripTextBox1.Text.Split('=')[1];
                        sqlPageText += "WHERE " + "" + whereFeild + "=" + "" + whereValue + " ";
                        sqlTotalText += "WHERE " + "" + whereFeild + "=" + "" + whereValue + " ";
                    }

                    sqlPageText += " ORDER BY " + _orderByColumn + " ";
                    sqlPageText += ") t1 ";
                    sqlPageText += "LIMIT " + pageSize + " OFFSET " + startIndex + ";";

                    #endregion

                    break;
            }           

            DataTable dt_pageData = db.QueryDataSet(CommandType.Text, sqlPageText, null).Tables[0];
            dt = dt_pageData;

            pager.RecordCount = recordCount;

            return recordCount;
        }

        private void toolButtonQuery_Click(object sender, EventArgs e)
        {
            pager1.PageCurrent = 1;
            pager1.Bind();
        }

        private void toolButtonCommit_Click(object sender, EventArgs e)
        {
            if (dgv_list.DataSource != null && _server != null)
            {
                DataSet ds = (dgv_list.DataSource as DataView).Table.DataSet;
                try
                {
                    using (var db = Utility.GetAdohelper(_server))
                    {
                        db.BeginTransaction();
                        try
                        {
                            string sqlText = "SELECT ";
                            switch (_server.ProviderName)
                            {
                                case "System.Data.SQL":
                                case "System.Data.Sql":
                                    sqlText += "TOP 0 * FROM  [" + _table + "] ;";
                                    break;
                                case "System.Data.Sqlite":
                                    sqlText += " * FROM  [" + _table + "] LIMIT 0 ;";
                                    break;
                                case "System.Data.PostgreSql":
                                    sqlText += " * FROM  \"" + _table + "\" LIMIT 0 ;";
                                    break;
                            }

                            db.UpdateDataSet(ds, sqlText);
                            db.Commit();

                            MessageBox.Show("已提交到数据库");
                        }
                        catch (Exception ex)
                        {
                            db.RollBack();
                            throw ex;
                        }                    
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }               

                ds.Dispose();
            }
        }

        private void 添加数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (this.dgv_list.DataSource != null)
            {
                DataTable dt = (this.dgv_list.DataSource as DataView).Table;
                DataRow newdatarow = dt.NewRow();
                dt.Rows.Add(newdatarow);
            }
        }

        private void 删除数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if(this.dgv_list .SelectedRows.Count >0)
            {
                List<DataGridViewRow> rows = new List<DataGridViewRow>();
                foreach (DataGridViewRow row in this.dgv_list.SelectedRows)
                {
                    rows.Add(row);
                }
                foreach (DataGridViewRow row in rows)
                {
                    this.dgv_list.Rows.Remove(row);
                }
            }
        }

        private void dgv_list_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == -1)
            {
                
            }
            else
            {
                
            }
        }

        private void dgv_list_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {   
            if(e.Button==MouseButtons.Right)
            {
                if (e.RowIndex > -1)
                {
                    this.dgv_list.ClearSelection();
                    this.dgv_list.Rows[e.RowIndex].Selected = true;
                }                    
            }
        }

        private void dgv_list_MouseClick(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                this.contextMenuStrip1.Show(this.dgv_list,e.Location);
            }
        }

        public override void OnThemeChanged(EventArgs e)
        {            
            Color foreColor = SystemColors.WindowText;
            Color backColor = SystemColors.Control;
            Color linkColor = System.Drawing.Color.Blue;
            Color toolStripBackColor = SystemColors.ControlLight;
            string themeName = AppSettings.EditorSettings.TSQLEditorTheme;
            switch (themeName)
            {
                case "Default":
                    foreColor = SystemColors.WindowText;
                    backColor = SystemColors.Control;
                    linkColor = System.Drawing.Color.Blue;
                    toolStripBackColor = Color.FromArgb(246, 248, 250);
                    dgv_list.AlternatingRowsDefaultCellStyle.BackColor = SystemColors.Control;
                    break;
                case "Black":
                    foreColor = Color.FromArgb(240, 240, 240);
                    backColor = Color.FromArgb(045, 045, 048);
                    linkColor = Color.FromArgb(051, 153, 153);
                    toolStripBackColor = Color.FromArgb(062, 062, 062);
                    dgv_list.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(036, 036, 036);
                    break;
            }
            this.toolStrip1.BackColor = toolStripBackColor;
            this.toolStrip1.SetTheme(themeName);
            this.dgv_list.SetTheme(themeName);
            this.pager1.SetTheme(themeName);
            this.ForeColor = foreColor;
            this.BackColor = backColor;
        }
    }
}
